
# Importing necessary libraries

import pandas as pd
import sqlalchemy as sq

# Reading Nuclear.csv file

df_nuclear = pd.read_csv("nuclear-energy-generation.csv")

#display(df_nuclear)

# Connecting to MySQL Database now.

engine = sq.create_engine('mysql+mysqlconnector://swarnim_khosla:9LPD90DMX@datasciencedb.ucalgary.ca/swarnim_khosla')

# Writing the Nuclear dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_nuclear.to_sql("nuclear", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_nuclear_table = pd.read_sql_table("nuclear", engine) # Reading the data.
df_nuclear_table.head(10)


# Checking those records where 'Code' is NULL.

# Query_1

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM nuclear
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_2

engine.execute('''SET SQL_SAFE_UPDATES = 0;''')

# Here we have disabled 'Safe Mode' in MySQL in order to delete those regions.

# Reference: - https://linuxhint.com/mysql-error-code-1175-during-update-mysql/

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4384254d0>


# Query_3

engine.execute('''DELETE FROM nuclear WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc9e950>


# We shall now confirm that those records have been deleted where Entity IS NULL.

# Query_4

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM nuclear;''', engine)

display(query_table)


# Query_5

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM nuclear
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_6

query_table = pd.read_sql_query('''SELECT * 
                                     FROM nuclear
                                     WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''', engine)

display(query_table)


# Query_7

engine.execute('''DELETE FROM nuclear WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''')

# Here we are deleting all the rows for Kosovo, USSR and World.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc9eb50>


# Query_8

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from Nuclear power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from nuclear (TWh)`) AS Total_Nuclear_Energy
                                     FROM nuclear
                                     GROUP BY `Entity`
                                     HAVING Total_Nuclear_Energy <> 0
                                     ORDER BY Total_Nuclear_Energy DESC;''', engine)

display(query_table)


# Query_9

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of nuclear energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM nuclear 
                                     WHERE `Electricity from nuclear (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from nuclear (TWh)`) DESC;''', engine)

display(query_table)


# Query_10

engine.execute('''ALTER TABLE nuclear RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc8d090>


# Query_11

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE nuclear;''', engine)

display(query_table)


# Reading hydropower-consumption.csv file

df_hydro = pd.read_csv("hydropower-consumption.csv")

#display(df_hydro)

# Writing the Hydro dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_hydro.to_sql("hydro", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_hydro_table = pd.read_sql_table("hydro", engine) # Reading the data.
df_hydro_table.head(10)


# Query_12

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM hydro
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_13

engine.execute('''DELETE FROM hydro WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4382bb4d0>


# Query_14

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM hydro;''', engine)

display(query_table)


# Query_15

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM hydro
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_16

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM hydro
                                     WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''', engine)

display(query_table)


# Query_17

engine.execute('''DELETE FROM hydro WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''')

# Here we are deleting all the rows for Kosovo, USSR and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bcbfb50>


# Query_18

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from Hydro power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from hydro (TWh)`) AS Total_Hydro_Energy
                                     FROM hydro
                                     GROUP BY `Entity`
                                     HAVING Total_Hydro_Energy <> 0
                                     ORDER BY Total_Hydro_Energy DESC;''', engine)

display(query_table)


# Query_19

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of hydro energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM hydro 
                                     WHERE `Electricity from hydro (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from hydro (TWh)`) DESC;''', engine)

display(query_table)


# Query_20

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE hydro RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff448391e10>


# Query_21

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE hydro;''', engine)

display(query_table)


# Reading wind-generation.csv file

df_wind = pd.read_csv("wind-generation.csv")

#display(df_wind)

# Writing the Wind dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_wind.to_sql("wind", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_wind_table = pd.read_sql_table("wind", engine) # Reading the data.
df_wind_table.head(10)


# Query_22

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM wind
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_23

engine.execute('''DELETE FROM wind WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff44836cfd0>


# Query_24

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM wind;''', engine)

display(query_table)


# Query_25

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM wind
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_26

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM wind
                                     WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''', engine)

display(query_table)


# Query_27

engine.execute('''DELETE FROM wind WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''')

# Here we are deleting all the rows for Kosovo, USSR and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bca3150>


# Query_28

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from wind power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from wind (TWh)`) AS Total_Wind_Energy
                                     FROM wind
                                     GROUP BY `Entity`
                                     HAVING Total_Wind_Energy <> 0
                                     ORDER BY Total_Wind_Energy DESC;''', engine)

display(query_table)


# Query_29

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of wind energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM wind 
                                     WHERE `Electricity from wind (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from wind (TWh)`) DESC;''', engine)

display(query_table)


# Query_30

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE wind RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff43988e850>


# Query_31

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE wind;''', engine)

display(query_table)


# Reading solar-energy-consumption.csv file

df_solar = pd.read_csv("solar-energy-consumption.csv")

#display(df_solar)

# Writing the Solar dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_solar.to_sql("solar", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_solar_table = pd.read_sql_table("solar", engine) # Reading the data.
df_solar_table.head(10)


# Query_32

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM solar
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_33

engine.execute('''DELETE FROM solar WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bca3f10>


# Query_34

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM solar;''', engine)

display(query_table)


# Query_35

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM solar
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_36

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM solar
                                     WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''', engine)

display(query_table)


# Query_37

engine.execute('''DELETE FROM solar WHERE `Entity` IN ('Kosovo', 'USSR', 'World');''')

# Here we are deleting all the rows for Kosovo, USSR and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc65210>


# Query_38

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from solar power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from solar (TWh)`) AS Total_Solar_Energy
                                     FROM solar
                                     GROUP BY `Entity`
                                     HAVING Total_Solar_Energy <> 0
                                     ORDER BY Total_Solar_Energy DESC;''', engine)

display(query_table)


# Query_39

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of solar energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM solar
                                     WHERE `Electricity from solar (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from solar (TWh)`) DESC;''', engine)

display(query_table)


# Query_40

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE solar RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc661d0>


# Query_41

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE solar;''', engine)

display(query_table)


# Reading electricity-coal.csv file

df_coal = pd.read_csv("electricity-coal.csv")

#display(df_coal)

# Writing the Coal dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_coal.to_sql("coal", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_coal_table = pd.read_sql_table("coal", engine) # Reading the data.
df_coal_table.head(10)


# Query_42

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM coal
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_43

engine.execute('''DELETE FROM coal WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff428044990>


# Query_44

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM coal;''', engine)

display(query_table)


# Query_45

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM coal
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_46

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM coal
                                     WHERE `Entity` IN ('Kosovo', 'World');''', engine)

display(query_table)


# Query_47

engine.execute('''DELETE FROM coal WHERE `Entity` IN ('Kosovo', 'World');''')

# Here we are deleting all the rows for Kosovo and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff42803e4d0>


# Query_48

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from coal power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from coal (TWh)`) AS Total_Coal_Energy
                                     FROM coal
                                     GROUP BY `Entity`
                                     HAVING Total_Coal_Energy <> 0
                                     ORDER BY Total_Coal_Energy DESC;''', engine)

display(query_table)


# Query_49

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of coal energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM coal
                                     WHERE `Electricity from coal (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from coal (TWh)`) DESC;''', engine)

display(query_table)


# Query_50

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE coal RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff428044310>


# Query_51

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE coal;''', engine)

display(query_table)


# Reading electricity-gas.csv file

df_gas = pd.read_csv("electricity-gas.csv")

#display(df_gas)

# Writing the Gas dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_gas.to_sql("gas", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_gas_table = pd.read_sql_table("gas", engine) # Reading the data.
df_gas_table.head(10)


# Query_52

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM gas
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_53

engine.execute('''DELETE FROM gas WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff428111c50>


# Query_54

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM gas;''', engine)

display(query_table)


# Query_55

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM gas
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_56

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM gas
                                     WHERE `Entity` IN ('Kosovo', 'World');''', engine)

display(query_table)


# Query_57

engine.execute('''DELETE FROM gas WHERE `Entity` IN ('Kosovo', 'World');''')

# Here we are deleting all the rows for Kosovo and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff428111150>


# Query_58

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from gas power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from gas (TWh)`) AS Total_Gas_Energy
                                     FROM gas
                                     GROUP BY `Entity`
                                     HAVING Total_Gas_Energy <> 0
                                     ORDER BY Total_Gas_Energy DESC;''', engine)

display(query_table)


# Query_59

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of gas energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM gas
                                     WHERE `Electricity from gas (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from gas (TWh)`) DESC;''', engine)

display(query_table)


# Query_60

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE gas RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f1bcd0>


# Query_61

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE gas;''', engine)

display(query_table)


# Reading electricity-oil.csv file

df_oil = pd.read_csv("electricity-oil.csv")

#display(df_oil)

# Writing the Oil dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_oil.to_sql("oil", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_oil_table = pd.read_sql_table("oil", engine) # Reading the data.
df_oil_table.head(10)


# Query_62

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM oil
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_63

engine.execute('''DELETE FROM oil WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff428111b50>


# Query_64

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM oil;''', engine)

display(query_table)


# Query_65

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM oil
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_66

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM oil
                                     WHERE `Entity` IN ('Kosovo', 'World');''', engine)

display(query_table)


# Query_66

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM oil
                                     WHERE `Entity` IN ('Kosovo', 'World');''', engine)

display(query_table)


# Query_67

engine.execute('''DELETE FROM oil WHERE `Entity` IN ('Kosovo', 'World');''')

# Here we are deleting all the rows for Kosovo and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f1b650>


# Query_68

# The query given below shall help us give distinct Entites and the sum of electricity they have generated from oil power.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Entity`), SUM(`Electricity from oil (TWh)`) AS Total_Oil_Energy
                                     FROM oil
                                     GROUP BY `Entity`
                                     HAVING Total_Oil_Energy <> 0
                                     ORDER BY Total_Oil_Energy DESC;''', engine)

display(query_table)


# Query_69

# The query given below shall tell us the starting year (oldest year) and the latest year (that is available in the dataset) of oil energy production.

query_table = pd.read_sql_query('''SELECT DISTINCT (`Entity`), MIN(`Year`) AS Oldest_year, MAX(`YEAR`) AS Latest_year, MAX(`YEAR`) -  MIN(`Year`) AS Duration  
                                     FROM oil
                                     WHERE `Electricity from oil (TWh)` <> 0
                                     GROUP BY `Entity`
                                     ORDER BY Duration DESC, SUM(`Electricity from oil (TWh)`) DESC;''', engine)

display(query_table)


# Query_70

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE oil RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f3ce90>


# Query_71

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE oil;''', engine)

display(query_table)


# Reading energy.csv file

df_total_electricity_generated = pd.read_csv("energy.csv")

#display(df_total_electricity_generated)

# Writing the Total Electricity Generated dataframe into a table
# Reference : - https://www.w3resource.com/pandas/dataframe/dataframe-to_sql.php

df_total_electricity_generated.to_sql("total_electricity", engine, if_exists = 'replace', index=False, method='multi') # Pushes all data to MySql

# Demonstartation of the fact that import has been successful.

df_total_electricity_table = pd.read_sql_table("total_electricity", engine) # Reading the data.
df_total_electricity_table.head(10)


# Query_72

# Checking those records where 'Code' is NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT `Entity` FROM total_electricity
                                     WHERE `Code` IS NULL;''', engine)

display(query_table)


# Query_73

engine.execute('''DELETE FROM total_electricity WHERE `CODE` IS NULL;''')

# Here we are deleting all the rows whose Entity was a Region and not a Country.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc8ddd0>


# Query_74

# We shall now confirm that those records have been deleted where Entity IS NULL.

query_table = pd.read_sql_query('''SELECT DISTINCT(`Code`), `Entity` FROM total_electricity;''', engine)

display(query_table)


# Query_75

query_table = pd.read_sql_query('''SELECT `Entity`, `Code`, CHAR_LENGTH(`CODE`)  AS Length 
                                     FROM total_electricity
                                     GROUP BY `Entity`
                                     HAVING CHAR_LENGTH(`CODE`) <> 3;''', engine)

# In this SQL query we are looking out for all records where Character length of 'Code' column is not equal to 3.

display(query_table)


# Query_76

# Seeing those records in its entirety where Code length NOT EQUAL to 3.

query_table = pd.read_sql_query('''SELECT * 
                                     FROM total_electricity
                                     WHERE `Entity` IN ('Kosovo', 'World');''', engine)

display(query_table)


# Query_77

engine.execute('''DELETE FROM total_electricity WHERE `Entity` IN ('Kosovo', 'World');''')

# Here we are deleting all the rows for Kosovo and World for the reasons as stated above.

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff43997edd0>


# Query_78

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Nuclear, MAX(`Year`) AS Latest_Year_Available_Nuclear
                                      FROM nuclear;''', engine)

display(query_table)


# Query_79

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Hydro, MAX(`Year`) AS Latest_Year_Available_Hydro 
                                      FROM hydro;''', engine)

display(query_table)


# Query_80

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Wind, MAX(`Year`) AS Latest_Year_Available_Wind 
                                      FROM wind;''', engine)

display(query_table)


# Query_81

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Solar, MAX(`Year`) AS Latest_Year_Available_Solar 
                                      FROM solar;''', engine)

display(query_table)


# Query_82

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Coal, MAX(`Year`) AS Latest_Year_Available_Coal 
                                      FROM coal;''', engine)

display(query_table)


# Query_83

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Gas, MAX(`Year`) AS Latest_Year_Available_Gas 
                                      FROM gas;''', engine)

display(query_table)


# Query_84

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Oil, MAX(`Year`) AS Latest_Year_Available_Oil 
                                      FROM oil;''', engine)

display(query_table)


# Query_85

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Total, MAX(`Year`) AS Latest_Year_Available_Total 
                                      FROM total_electricity;''', engine)

display(query_table)


# Deleting the rows where year is from 1965-1984 in Nuclear, Hydro, Wind and Solar dataset.

# Query_86

engine.execute('''DELETE FROM nuclear WHERE `Year` BETWEEN 1965 AND 1984;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46c04ef90>


# Query_87

engine.execute('''DELETE FROM hydro WHERE `Year` BETWEEN 1965 AND 1984;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff439a18190>


# Query_88

engine.execute('''DELETE FROM wind WHERE `Year` BETWEEN 1965 AND 1984;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46c04e350>


# Query_89

engine.execute('''DELETE FROM solar WHERE `Year` BETWEEN 1965 AND 1984;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff439a0d590>


# Query_90

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Nuclear, MAX(`Year`) AS Latest_Year_Available_Nuclear
                                      FROM nuclear;''', engine)

display(query_table)


# Query_91

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Hydro, MAX(`Year`) AS Latest_Year_Available_Hydro 
                                      FROM hydro;''', engine)

display(query_table)


# Query_92

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Wind, MAX(`Year`) AS Latest_Year_Available_Wind 
                                      FROM wind;''', engine)

display(query_table)


# Query_93

query_table = pd.read_sql_query('''SELECT MIN(`Year`) AS Oldest_Year_Available_Solar, MAX(`Year`) AS Latest_Year_Available_Solar 
                                      FROM solar;''', engine)

display(query_table)


# Query_94

# We shall now change the column name from Entity to Country because now our Entity column only consists of various countries and no regions.

engine.execute('''ALTER TABLE total_electricity RENAME COLUMN Entity TO Country;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f72050>


# Query_95

# Checking whether the column name has been changed or not.

query_table = pd.read_sql_query('''DESCRIBE total_electricity;''', engine)

display(query_table)


# Query_96

query_table = pd.read_sql_query('''SELECT DISTINCT(`Country`), 
                                      SUM(`Electricity generation (TWh)`) AS Total_Electricity_Generated, 
                                      MAX(`YEAR`) -  MIN(`Year`) AS Duration, 
                                      (SUM(`Electricity generation (TWh)`)/ (MAX(`YEAR`) -  MIN(`Year`))) AS Average_Consumption_Per_Year
                                      FROM total_electricity
                                      GROUP BY `Country`
                                      HAVING Total_Electricity_Generated <> 0
                                      ORDER BY Duration DESC, Total_Electricity_Generated DESC;''', engine)

display(query_table)


# Query_97

query_table = pd.read_sql_query('''SELECT `Country`, SUM(`Electricity generation (TWh)`) AS Total_Electricity_Generated  
                                      FROM total_electricity
                                      GROUP BY `Country`
                                      HAVING Total_Electricity_Generated = 0;''', engine)

display(query_table)


# Query_98

engine.execute('''DELETE FROM nuclear WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46c04eb90>


# Query_99

engine.execute('''DELETE FROM hydro WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4399ad9d0>


# Query_100

engine.execute('''DELETE FROM wind WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438fc0cd0>


# Query_101

engine.execute('''DELETE FROM solar WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46c039110>


# Query_102

engine.execute('''DELETE FROM coal WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46c0cb790>


# Query_103

engine.execute('''DELETE FROM gas WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4399ad990>


# Query_104

engine.execute('''DELETE FROM oil WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f72350>


# Query_105

engine.execute('''DELETE FROM total_electricity WHERE `Country` REGEXP 'Bermuda|Gibraltar|Niue|Saint Helena';''')

# Reference: - https://stackoverflow.com/a/1127106/10912105

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff438f729d0>


# Query_106

# Now we shall find out the extra values which are in Nuclear table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT nuclear.`Country`, nuclear.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM nuclear 
                                      LEFT JOIN total_electricity
                                      ON nuclear.`Country` = total_electricity.`Country` AND nuclear.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_107

# Now we shall find out the extra values which are in Hydro table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT hydro.`Country`, hydro.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM hydro 
                                      LEFT JOIN total_electricity
                                      ON hydro.`Country` = total_electricity.`Country` AND hydro.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_108

# Now we shall find out the extra values which are in Wind table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT wind.`Country`, wind.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM wind 
                                      LEFT JOIN total_electricity
                                      ON wind.`Country` = total_electricity.`Country` AND wind.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_109

# Now we shall find out the extra values which are in Solar table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT solar.`Country`, solar.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM solar 
                                      LEFT JOIN total_electricity
                                      ON solar.`Country` = total_electricity.`Country` AND solar.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_110

# Now we shall find out the extra values which are in Coal table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT coal.`Country`, coal.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM coal 
                                      LEFT JOIN total_electricity
                                      ON coal.`Country` = total_electricity.`Country` AND coal.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_111

# Now we shall find out the extra values which are in Gas table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT gas.`Country`, gas.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM gas 
                                      LEFT JOIN total_electricity
                                      ON gas.`Country` = total_electricity.`Country` AND gas.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_112

# Now we shall find out the extra values which are in Oil table but not in Total Electricity Generated table using JOINS.

# Reference: - https://stackoverflow.com/a/9954815/10912105

query_table = pd.read_sql_query('''SELECT oil.`Country`, oil.`Year`, total_electricity.`Country`, total_electricity.`Year` 
                                      FROM oil 
                                      LEFT JOIN total_electricity
                                      ON oil.`Country` = total_electricity.`Country` AND oil.`Year` = total_electricity.`Year`
                                      WHERE total_electricity.`Country` IS NULL OR total_electricity.`Year` IS NULL;''', engine)

display(query_table)


# Query_113

# Number of records in Nuclear table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Nuclear_Count FROM nuclear;''', engine)

display(query_table)


# Query_114

# Number of records in Hydro table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Hydro_Count FROM hydro;''', engine)

display(query_table)


# Query_115

# Number of records in Wind table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Wind_Count FROM wind;''', engine)

display(query_table)


# Query_116

# Number of records in Solar table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Solar_Count FROM solar;''', engine)

display(query_table)


# Query_117

# Number of records in Coal table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Coal_Count FROM coal;''', engine)

display(query_table)


# Query_118

# Number of records in Gas table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Gas_Count FROM gas;''', engine)

display(query_table)


# Query_119

# Number of records in Oil table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Oil_Count FROM oil;''', engine)

display(query_table)


# Query_120

# Number of records in Total_electricity table

query_table = pd.read_sql_query('''SELECT COUNT(*) AS Total_Count FROM total_electricity;''', engine)

display(query_table)


# Query_121

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      nuclear.`Country` AS Nuclear_Country, 
                                      nuclear.`Year` AS Nuclear_Year
                                      FROM nuclear 
                                      RIGHT JOIN total_electricity
                                      ON nuclear.`Country` = total_electricity.`Country` AND nuclear.`Year` = total_electricity.`Year`
                                      WHERE nuclear.`Country` IS NULL OR nuclear.`Year` IS NULL;''', engine)

display(query_table)


# Query_122

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      hydro.`Country` AS Hydro_Country, 
                                      hydro.`Year` AS Hydro_Year
                                      FROM hydro
                                      RIGHT JOIN total_electricity
                                      ON hydro.`Country` = total_electricity.`Country` AND hydro.`Year` = total_electricity.`Year`
                                      WHERE hydro.`Country` IS NULL OR hydro.`Year` IS NULL;''', engine)

display(query_table)


# Query_123

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      wind.`Country` AS Wind_Country, 
                                      wind.`Year` AS Wind_Year
                                      FROM wind
                                      RIGHT JOIN total_electricity
                                      ON wind.`Country` = total_electricity.`Country` AND wind.`Year` = total_electricity.`Year`
                                      WHERE wind.`Country` IS NULL OR wind.`Year` IS NULL;''', engine)

display(query_table)


# Query_124

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      solar.`Country` AS Solar_Country, 
                                      solar.`Year` AS Solar_Year
                                      FROM solar
                                      RIGHT JOIN total_electricity
                                      ON solar.`Country` = total_electricity.`Country` AND solar.`Year` = total_electricity.`Year`
                                      WHERE solar.`Country` IS NULL OR solar.`Year` IS NULL;''', engine)

display(query_table)


# Query_125

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      coal.`Country` AS Coal_Country, 
                                      coal.`Year` AS Coal_Year
                                      FROM coal
                                      RIGHT JOIN total_electricity
                                      ON coal.`Country` = total_electricity.`Country` AND coal.`Year` = total_electricity.`Year`
                                      WHERE coal.`Country` IS NULL OR coal.`Year` IS NULL;''', engine)

display(query_table)


# Query_126

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      gas.`Country` AS Gas_Country, 
                                      gas.`Year` AS Gas_Year
                                      FROM gas
                                      RIGHT JOIN total_electricity
                                      ON gas.`Country` = total_electricity.`Country` AND gas.`Year` = total_electricity.`Year`
                                      WHERE gas.`Country` IS NULL OR gas.`Year` IS NULL;''', engine)

display(query_table)


# Query_127

query_table = pd.read_sql_query('''SELECT total_electricity.`Country` AS Total_Country, 
                                      total_electricity.`Year` AS Total_Year, 
                                      oil.`Country` AS Oil_Country, 
                                      oil.`Year` AS Oil_Year
                                      FROM oil
                                      RIGHT JOIN total_electricity
                                      ON oil.`Country` = total_electricity.`Country` AND oil.`Year` = total_electricity.`Year`
                                      WHERE oil.`Country` IS NULL OR oil.`Year` IS NULL;''', engine)

display(query_table)


# Query_128

# Since we would be dividing by Total Electricity Generated, we must make sure there are no more NULL values / 0 values in the denominator.

# Therefore we would delete all those records which have 0/NULL anywhere in the Total Electricity Generated.

engine.execute('''DELETE FROM total_electricity WHERE total_electricity.`Electricity generation (TWh)` = 0 OR total_electricity.`Electricity generation (TWh)` IS NULL;''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff46bc8d610>


# Query_129

# Now we shall create a table exclusively for Clean Electricity Energy Generation

# References: - 

# https://stackoverflow.com/a/5859469/10912105

engine.execute('''
CREATE TEMPORARY TABLE IF NOT EXISTS clean_generation AS 
(SELECT
	total_electricity.`Country` AS Country, 
	total_electricity.`Year` AS Year, 
	nuclear.`Electricity from nuclear (TWh)` AS Nuclear,
    hydro.`Electricity from hydro (TWh)` AS Hydro,
    wind.`Electricity from wind (TWh)` AS Wind,
    solar.`Electricity from solar (TWh)` AS Solar,
    total_electricity.`Electricity generation (TWh)` AS Total,
    (((nuclear.`Electricity from nuclear (TWh)` + hydro.`Electricity from hydro (TWh)` + wind.`Electricity from wind (TWh)` + solar.`Electricity from solar (TWh)`)/total_electricity.`Electricity generation (TWh)`)*100) AS Clean_Percentage
FROM
	total_electricity
INNER JOIN 
	nuclear 
ON
	total_electricity.`Country` = nuclear.`Country` AND total_electricity.`Year` = nuclear.`Year`
INNER JOIN 
	hydro 
ON
	total_electricity.`Country` = hydro.`Country` AND total_electricity.`Year` = hydro.`Year`
INNER JOIN 
	wind 
ON
	total_electricity.`Country` = wind.`Country` AND total_electricity.`Year` = wind.`Year`
INNER JOIN 
	solar
ON
	total_electricity.`Country` = solar.`Country` AND total_electricity.`Year` = solar.`Year`
GROUP BY 
	total_electricity.`Country`, total_electricity.`Year`);
''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4783e3590>


# Query_130

# Let's see our table

query_table = pd.read_sql_query('''SELECT * FROM clean_generation;''', engine)

display(query_table)


# Query_131

# Exploring our clean_generation table

query_table = pd.read_sql_query('''SELECT * FROM clean_generation
                                   GROUP BY `Country`, `Year`
                                   ORDER BY `Total` DESC, `Clean_Percentage` DESC;''', engine)

display(query_table)


# Query_132

# Now we shall create a table exclusively for Dirty Electricity Energy Generation

# References: - 

# https://stackoverflow.com/a/5859469/10912105

engine.execute('''
CREATE TEMPORARY TABLE IF NOT EXISTS dirty_generation AS 
(SELECT
	total_electricity.`Country` AS Country, 
	total_electricity.`Year` AS Year, 
	coal.`Electricity from coal (TWh)` AS Coal,
    gas.`Electricity from gas (TWh)` AS Gas,
    oil.`Electricity from oil (TWh)` AS Oil,
    total_electricity.`Electricity generation (TWh)` AS Total,
    (((coal.`Electricity from coal (TWh)` + gas.`Electricity from gas (TWh)` + oil.`Electricity from oil (TWh)`)/total_electricity.`Electricity generation (TWh)`)*100) AS Dirty_Percentage
FROM
	total_electricity
INNER JOIN 
	coal 
ON
	total_electricity.`Country` = coal.`Country` AND total_electricity.`Year` = coal.`Year`
INNER JOIN 
	gas 
ON
	total_electricity.`Country` = gas.`Country` AND total_electricity.`Year` = gas.`Year`
INNER JOIN 
	oil 
ON
	total_electricity.`Country` = oil.`Country` AND total_electricity.`Year` = oil.`Year`
GROUP BY 
	total_electricity.`Country`, total_electricity.`Year`);
''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff4397daa50>


# Query_133

# Let's see our table

query_table = pd.read_sql_query('''SELECT * FROM dirty_generation;''', engine)

display(query_table)


# Query_134

# Exploring our dirty_generation table

query_table = pd.read_sql_query('''SELECT * FROM dirty_generation
                                   GROUP BY `Country`, `Year`
                                   ORDER BY `Total` DESC, `Dirty_Percentage` DESC;''', engine)

display(query_table)


# Query_135

# Joining our clean_generation table and dirty_generation table to make our final table.

engine.execute('''
CREATE TEMPORARY TABLE IF NOT EXISTS meta_generation AS
(SELECT 
	clean_generation.`Country`,
    clean_generation.`Year`,
    clean_generation.`Nuclear`,
    clean_generation.`Hydro`,
    clean_generation.`Wind`,
    clean_generation.`Solar`,
    dirty_generation.`Coal`,
    dirty_generation.`Gas`,
    dirty_generation.`Oil`,
    clean_generation.`Total`,
    clean_generation.`Clean_Percentage`,
    dirty_generation.`Dirty_Percentage`
FROM 
	clean_generation
INNER JOIN
	dirty_generation
ON
	clean_generation.`Country` = dirty_generation.`Country` AND clean_generation.`Year` = dirty_generation.`Year`);
''')

<sqlalchemy.engine.cursor.LegacyCursorResult at 0x7ff44010cb10>


# Query_136

# Let's see our final table

query_table = pd.read_sql_query('''SELECT * FROM meta_generation;''', engine)

display(query_table)


# Query_137

# Exploring our meta_generation table

query_table = pd.read_sql_query('''SELECT * FROM meta_generation
                                   GROUP BY `Country`, `Year`
                                   ORDER BY `Total` DESC''', engine)

display(query_table)


# Query_138

# Taking out the ranks for relevant columns.

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`, 
	MAX(`Year`) AS Latest_Year,
    (`Nuclear` + `Hydro` + `Wind` + `Solar`) AS Clean_generation,
    (`Coal` + `Gas` + `Oil`) AS Dirty_generation,
    `Total`,
    `Clean_Percentage`,
    `Dirty_Percentage`,
    RANK() OVER(ORDER BY Clean_generation DESC) AS Clean_Rank,
    RANK() OVER(ORDER BY Dirty_generation DESC) AS Dirty_Rank,
    RANK() OVER(ORDER BY Clean_Percentage DESC) AS Clean_Percentage_Rank,
    RANK() OVER(ORDER BY Dirty_Percentage DESC) AS Dirty_Percentage_Rank
FROM 
	meta_generation
WHERE
    `YEAR` = 2021
GROUP BY 
	`Country`, `Year`
ORDER BY 
	`Total` DESC;                                   
''', engine)

display(query_table)


# Query_139

# Now we will only display those data which has complete `Year` values i.e. from 1985 till 2021.

# Reference: - https://stackoverflow.com/a/74871098/10912105

query_table = pd.read_sql_query(
'''
WITH Countries
AS
	(
	SELECT `Country`
	FROM meta_generation
	WHERE `Year` BETWEEN 1985 AND 2021
	GROUP BY `Country`
	HAVING COUNT(DISTINCT Year) = 37
	)

SELECT *
FROM meta_generation  
INNER JOIN Countries ON meta_generation.`Country` = Countries.`Country`;                                   
''', engine)

display(query_table)


# Query_140

# Taking out the ranks for relevant columns from the subset of our dataset (only choosing countries with years from year 1985 till 2021).

query_table = pd.read_sql_query(
'''
WITH Countries
AS
	(
	SELECT `Country`
	FROM meta_generation
	WHERE `Year` BETWEEN 1985 AND 2021
	GROUP BY `Country`
	HAVING COUNT(DISTINCT Year) = 37
	)

SELECT 
	meta_generation.`Country`, 
	MAX(`Year`) AS Latest_Year,
    (`Nuclear` + `Hydro` + `Wind` + `Solar`) AS Clean_generation,
    (`Coal` + `Gas` + `Oil`) AS Dirty_generation,
    `Total`,
    `Clean_Percentage`,
    `Dirty_Percentage`,
    RANK() OVER(ORDER BY Clean_generation DESC) AS Clean_Rank,
    RANK() OVER(ORDER BY Dirty_generation DESC) AS Dirty_Rank,
    RANK() OVER(ORDER BY Clean_Percentage DESC) AS Clean_Percentage_Rank,
    RANK() OVER(ORDER BY Dirty_Percentage DESC) AS Dirty_Percentage_Rank
FROM 
	meta_generation
INNER JOIN 
    Countries ON meta_generation.`Country` = Countries.`Country`     
WHERE
    `YEAR` = 2021
GROUP BY 
	meta_generation.`Country`, `Year`
ORDER BY 
	`Total` DESC;                                    
''', engine)

display(query_table)


# Query_141

# Doing more cleanup.

engine.execute(
'''
CREATE TEMPORARY TABLE IF NOT EXISTS Countries AS
(
	SELECT `Country`
	FROM meta_generation
	WHERE `Year` BETWEEN 1985 AND 2021
	GROUP BY `Country`
	HAVING COUNT(DISTINCT Year) = 37
);                                    
''')


query_table = pd.read_sql_query(
'''
SELECT * FROM meta_generation WHERE meta_generation.`Country` IN (SELECT * FROM Countries);                                   
''', engine)

display(query_table)


# We would now display the results of only Nuclear energy

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Nuclear`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Nuclear` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Nuclear` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Nuclear", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,1000])

fig.show()


# We would now display the results of only Hydro energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Hydro`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Hydro` > 10
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Hydro` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Hydro", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,1500])

fig.show()


# We would now display the results of only Wind energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Wind`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Wind` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Wind` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Wind", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,700])

fig.show()


# We would now display the results of only Solar energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Solar`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Solar` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Solar` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Solar", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,350])

fig.show()


# We would now display the results of only Coal energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Coal`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Coal` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Coal` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Coal", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,5500])

fig.show()


# We would now display the results of only Gas energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Gas`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Gas` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Gas` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Gas", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,2000])

fig.show()


# We would now display the results of only Oil energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Oil`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Oil` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Oil` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Oil", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,300])

fig.show()


# We would now display the results of Total energy.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Total` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Total` DESC;                                 
''', engine)


fig = px.bar(query_table, x="Country", y="Total", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,9000])

fig.show()


# We would now display the results of Clean energy %.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Clean_Percentage`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Clean_Percentage` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Clean_Percentage` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Clean_Percentage", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,100])

fig.show()


# We would now display the results of Dirty energy %.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    `Dirty_Percentage`,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries) AND `Dirty_Percentage` <> 0
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Dirty_Percentage` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Dirty_Percentage", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,100])

fig.show()


# We would now display the results of Total Clean energy generated.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    (`Nuclear` + `Hydro` + `Wind` + `Solar`) AS Clean_generation,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries)
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Clean_generation` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Clean_generation", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,3000])

fig.show()


# We would now display the results of Total Dirty energy generated.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	`Country`,
    `Year`,
    (`Coal` + `Gas` + `Oil`) AS Dirty_generation,
    `Total`
FROM 
	meta_generation 
WHERE 
	meta_generation.`Country` IN (SELECT * FROM Countries)
GROUP BY
	`Country`, 
    `Year`
ORDER BY
	`Year` ASC,
    `Dirty_generation` DESC;                                  
''', engine)


fig = px.bar(query_table, x="Country", y="Dirty_generation", color="Total",
  animation_frame="Year", animation_group="Country", range_y=[1,6000])

fig.show()


# We would now display the results of the energy situation for the latest year i.e. 2021.

import plotly.express as px

query_table = pd.read_sql_query(
'''
SELECT 
	meta_generation.`Country`, 
	MAX(`Year`) AS Latest_Year,
    (`Nuclear` + `Hydro` + `Wind` + `Solar`) AS Clean_generation,
    (`Coal` + `Gas` + `Oil`) AS Dirty_generation,
    `Total`,
    `Clean_Percentage`,
    `Dirty_Percentage`,
    RANK() OVER(ORDER BY Clean_generation DESC) AS Clean_Rank,
    RANK() OVER(ORDER BY Dirty_generation DESC) AS Dirty_Rank,
    RANK() OVER(ORDER BY Clean_Percentage DESC) AS Clean_Percentage_Rank,
    RANK() OVER(ORDER BY Dirty_Percentage DESC) AS Dirty_Percentage_Rank
FROM 
	meta_generation   
WHERE
    `Country` IN (SELECT * FROM Countries) AND `YEAR` = 2021
GROUP BY 
	meta_generation.`Country`, `Year`
ORDER BY 
	`Total` DESC;                                  
''', engine)



fig = px.bar(query_table, x="Country", y=[ "Dirty_generation", "Clean_generation"], text_auto= ".2s", title="Energy usage for year 2021")

fig.update_traces(textfont_size=12, textangle=0, textposition="outside", cliponaxis=False)

fig.show()


# Disposing SQLAlchemy engine object.
engine.dispose()

	Entity	Code	Year
0	Afghanistan	AFG	2000
1	Afghanistan	AFG	2001
2	Afghanistan	AFG	2002
3	Afghanistan	AFG	2003
4	Afghanistan	AFG	2004
5	Afghanistan	AFG	2005
6	Afghanistan	AFG	2006
7	Afghanistan	AFG	2007
8	Afghanistan	AFG	2008
9	Afghanistan	AFG	2009

	Entity
0	Africa
1	Africa (BP)
2	Asia
3	Asia Pacific (BP)
4	CIS (BP)
5	Central America (BP)
6	Eastern Africa (BP)
7	Europe
8	Europe (BP)
9	European Union (27)
10	G20 (Ember)
11	G7 (Ember)
12	High-income countries
13	Latin America and Caribbean (Ember)
14	Low-income countries
15	Lower-middle-income countries
16	Middle Africa (BP)
17	Middle East (BP)
18	Middle East (Ember)
19	Non-OECD (BP)
20	North America
21	North America (BP)
22	OECD (BP)
23	OECD (Ember)
24	Oceania
25	Other Africa (BP)
26	Other Asia Pacific (BP)
27	Other CIS (BP)
28	Other Caribbean (BP)
29	Other Europe (BP)
30	Other Middle East (BP)
31	Other Northern Africa (BP)
32	Other South America (BP)
33	Other South and Central America (BP)
34	Other Southern Africa (BP)
35	South America
36	South and Central America (BP)
37	Upper-middle-income countries
38	Western Africa (BP)

	Code	Entity
0	AFG	Afghanistan
1	ALB	Albania
2	DZA	Algeria
3	ASM	American Samoa
4	AGO	Angola
...	...	...
212	ESH	Western Sahara
213	OWID_WRL	World
214	YEM	Yemen
215	ZMB	Zambia
216	ZWE	Zimbabwe

	Entity	Code	Year	Electricity from nuclear (TWh)
0	Kosovo	OWID_KOS	2000	0.000000
1	Kosovo	OWID_KOS	2001	0.000000
2	Kosovo	OWID_KOS	2002	0.000000
3	Kosovo	OWID_KOS	2003	0.000000
4	Kosovo	OWID_KOS	2004	0.000000
...	...	...	...	...
93	World	OWID_WRL	2017	2637.234619
94	World	OWID_WRL	2018	2700.464111
95	World	OWID_WRL	2019	2796.354004
96	World	OWID_WRL	2020	2693.978516
97	World	OWID_WRL	2021	2800.267822

	Entity	Total_Nuclear_Energy
0	United States	30585.713050
1	France	15084.615879
2	Japan	7974.783257
3	Germany	5608.414777
4	Russia	5536.170525
5	South Korea	4061.015937
6	Canada	3640.816111
7	United Kingdom	3380.199711
8	China	3103.685854
9	Ukraine	2900.260307
10	Sweden	2729.684004
11	Spain	2188.061997
12	Belgium	1738.848128
13	Taiwan	1400.442735
14	Switzerland	1055.958107
15	Finland	897.833579
16	India	759.094873
17	Czechia	749.470882
18	Bulgaria	666.009738
19	Slovakia	557.227974
20	Hungary	525.662389
21	South Africa	430.291888
22	Brazil	335.832116
23	Lithuania	301.926648
24	Argentina	294.219996
25	Mexico	292.558012
26	Romania	224.006400
27	Netherlands	184.997708
28	Slovenia	171.910687
29	Pakistan	110.190269
30	Italy	89.892000
31	Iran	49.973792
32	Armenia	49.240000
33	United Arab Emirates	12.168030
34	Belarus	6.118000
35	Kazakhstan	2.488122

	Entity	Oldest_year	Latest_year	Duration
0	United States	1965	2021	56
1	France	1965	2021	56
2	Japan	1965	2021	56
3	Germany	1965	2021	56
4	Canada	1965	2021	56
5	United Kingdom	1965	2021	56
6	Sweden	1965	2021	56
7	Belgium	1966	2021	55
8	Spain	1968	2021	53
9	Netherlands	1968	2021	53
10	Switzerland	1969	2021	52
11	India	1969	2021	52
12	Pakistan	1971	2021	50
13	Slovakia	1972	2021	49
14	Bulgaria	1974	2021	47
15	Argentina	1974	2021	47
16	South Korea	1977	2021	44
17	Taiwan	1977	2021	44
18	Finland	1977	2021	44
19	Czechia	1983	2021	38
20	Hungary	1983	2021	38
21	South Africa	1984	2021	37
22	Brazil	1984	2021	37
23	Russia	1985	2021	36
24	Ukraine	1985	2021	36
25	Mexico	1989	2021	32
26	Slovenia	1990	2021	31
27	China	1993	2021	28
28	Romania	1996	2021	25
29	Lithuania	1985	2009	24
30	Italy	1965	1987	22
31	Armenia	2000	2021	21
32	Iran	2011	2021	10
33	Kazakhstan	1989	1998	9
34	United Arab Emirates	2020	2021	1
35	Belarus	2020	2021	1

	Field	Type	Null	Default
0	Country	text	YES	None
1	Code	text	YES	None
2	Year	bigint(20)	YES	None
3	Electricity from nuclear (TWh)	double	YES	None

	Entity	Code	Year	Electricity from hydro (TWh)
0	Afghanistan	AFG	2000	0.31
1	Afghanistan	AFG	2001	0.50
2	Afghanistan	AFG	2002	0.56
3	Afghanistan	AFG	2003	0.63
4	Afghanistan	AFG	2004	0.56
5	Afghanistan	AFG	2005	0.59
6	Afghanistan	AFG	2006	0.64
7	Afghanistan	AFG	2007	0.75
8	Afghanistan	AFG	2008	0.54
9	Afghanistan	AFG	2009	0.78

	Entity	Total_Hydro_Energy
0	China	19810.906448
1	Canada	16948.565735
2	United States	15673.951126
3	Brazil	13023.484613
4	Russia	6362.678513
...	...	...
149	Estonia	0.509754
150	Saint Vincent and the Grenadines	0.490000
151	Sao Tome and Principe	0.180000
152	Turkmenistan	0.131225
153	Guadeloupe	0.030000

	Entity	Total_Wind_Energy
0	China	3207.577394
1	United States	2983.782956
2	Germany	1283.028996
3	Spain	810.899940
4	India	591.921919
...	...	...
109	Seychelles	0.030000
110	Uzbekistan	0.015746
111	Hong Kong	0.013532
112	Armenia	0.010000
113	United Arab Emirates	0.005956

	Entity	Oldest_year	Latest_year	Duration
0	Denmark	1978	2021	43
1	United States	1983	2021	38
2	Sweden	1983	2021	38
3	Canada	1985	2021	36
4	Germany	1986	2021	35
...	...	...	...	...
109	Seychelles	2013	2015	2
110	Uzbekistan	2019	2021	2
111	United Arab Emirates	2013	2015	2
112	El Salvador	2021	2021	0
113	Armenia	2010	2010	0

	Entity	Total_Solar_Energy
0	China	1252.639076
1	United States	749.182240
2	Japan	492.576158
3	Germany	440.263997
4	India	260.949518
...	...	...
149	Democratic Republic of Congo	0.040000
150	Sierra Leone	0.040000
151	Vanuatu	0.030000
152	Benin	0.020000
153	Latvia	0.015913

	Entity	Total_Coal_Energy
0	China	79501.712036
1	United States	64988.924500
2	India	20287.559601
3	Germany	10224.626053
4	Japan	8894.512680
...	...	...
81	Syria	1.480000
82	Norway	1.360000
83	Namibia	0.600000
84	Georgia	0.050000
85	Estonia	0.040000

	Entity	Total_Gas_Energy
0	United States	31484.557709
1	Russia	16958.225281
2	Japan	10093.155701
3	Iran	4303.379135
4	United Kingdom	3697.165621
...	...	...
104	El Salvador	5.190000
105	Palestine	2.910000
106	Tajikistan	2.600000
107	Panama	1.820000
108	Bosnia and Herzegovina	0.190000

	Entity	Code	Year	Electricity from oil (TWh)
0	Afghanistan	AFG	2000	0.16
1	Afghanistan	AFG	2001	0.09
2	Afghanistan	AFG	2002	0.13
3	Afghanistan	AFG	2003	0.31
4	Afghanistan	AFG	2004	0.33
5	Afghanistan	AFG	2005	0.34
6	Afghanistan	AFG	2006	0.20
7	Afghanistan	AFG	2007	0.20
8	Afghanistan	AFG	2008	0.19
9	Afghanistan	AFG	2009	0.16

	Entity	Total_Oil_Energy
0	Japan	6274.642496
1	Saudi Arabia	3389.124531
2	United States	3160.069517
3	Italy	2278.027412
4	Mexico	2234.671963
...	...	...
187	Moldova	0.160000
188	United Arab Emirates	0.096014
189	Nigeria	0.090000
190	Iceland	0.060000
191	Paraguay	0.050000

	Entity	Code	Year	Electricity generation (TWh)
0	Afghanistan	AFG	2000	0.47
1	Afghanistan	AFG	2001	0.59
2	Afghanistan	AFG	2002	0.69
3	Afghanistan	AFG	2003	0.94
4	Afghanistan	AFG	2004	0.89
5	Afghanistan	AFG	2005	0.93
6	Afghanistan	AFG	2006	0.84
7	Afghanistan	AFG	2007	0.95
8	Afghanistan	AFG	2008	0.73
9	Afghanistan	AFG	2009	0.94

	Country	Total_Electricity_Generated	Duration	Average_Consumption_Per_Year
0	United States	144090.67250	36	4002.518681
1	China	111040.88900	36	3084.469139
2	Japan	37490.91565	36	1041.414324
3	Russia	36870.26270	36	1024.173964
4	India	28417.13628	36	789.364897
...	...	...	...	...
205	Montserrat	0.39000	20	0.019500
206	Falkland Islands	0.35000	20	0.017500
207	Timor	0.50000	17	0.029412
208	Montenegro	52.90000	16	3.306250
209	South Sudan	4.49000	8	0.561250

	Total_Country	Total_Year	Hydro_Country	Hydro_Year
0	Kuwait	2021	None	None
1	Oman	2021	None	None
2	Qatar	2021	None	None
3	United Arab Emirates	2021	None	None

	Total_Country	Total_Year	Wind_Country	Wind_Year
0	Azerbaijan	1985	None	None
1	Azerbaijan	1986	None	None
2	Azerbaijan	1987	None	None
3	Azerbaijan	1988	None	None
4	Azerbaijan	1989	None	None
5	Belarus	1985	None	None
6	Belarus	1986	None	None
7	Belarus	1987	None	None
8	Belarus	1988	None	None
9	Belarus	1989	None	None
10	Estonia	1985	None	None
11	Estonia	1986	None	None
12	Estonia	1987	None	None
13	Estonia	1988	None	None
14	Estonia	1989	None	None
15	Kazakhstan	1985	None	None
16	Kazakhstan	1986	None	None
17	Kazakhstan	1987	None	None
18	Kazakhstan	1988	None	None
19	Kazakhstan	1989	None	None
20	Latvia	1985	None	None
21	Latvia	1986	None	None
22	Latvia	1987	None	None
23	Latvia	1988	None	None
24	Latvia	1989	None	None
25	Lithuania	1985	None	None
26	Lithuania	1986	None	None
27	Lithuania	1987	None	None
28	Lithuania	1988	None	None
29	Lithuania	1989	None	None
30	Russia	1985	None	None
31	Russia	1986	None	None
32	Russia	1987	None	None
33	Russia	1988	None	None
34	Russia	1989	None	None
35	Turkmenistan	1985	None	None
36	Turkmenistan	1986	None	None
37	Turkmenistan	1987	None	None
38	Turkmenistan	1988	None	None
39	Turkmenistan	1989	None	None
40	Ukraine	1985	None	None
41	Ukraine	1986	None	None
42	Ukraine	1987	None	None
43	Ukraine	1988	None	None
44	Ukraine	1989	None	None
45	Uzbekistan	1985	None	None
46	Uzbekistan	1986	None	None
47	Uzbekistan	1987	None	None
48	Uzbekistan	1988	None	None
49	Uzbekistan	1989	None	None

	Country	Year	Nuclear	Hydro	Wind	Solar	Total	Clean_Percentage
0	China	2021	407.500000	1300.000000	655.599976	327.000000	8534.250	31.521223
1	China	2020	366.200012	1321.708984	466.500000	261.100006	7779.060	31.051425
2	China	2019	348.700012	1272.537964	405.299988	224.000000	7503.428	29.993464
3	China	2018	295.000000	1198.886963	365.799988	176.899994	7166.133	28.419609
4	China	2017	248.100006	1165.064941	304.600006	117.800003	6604.447	27.792864
...	...	...	...	...	...	...	...	...
5586	Kiribati	2001	0.000000	0.000000	0.000000	0.000000	0.010	0.000000
5587	Kiribati	2002	0.000000	0.000000	0.000000	0.000000	0.010	0.000000
5588	Montserrat	2000	0.000000	0.000000	0.000000	0.000000	0.010	0.000000
5589	Montserrat	2001	0.000000	0.000000	0.000000	0.000000	0.010	0.000000
5590	Montserrat	2020	0.000000	0.000000	0.000000	0.000000	0.010	0.000000

	Country	Year	Coal	Gas	Oil	Total	Dirty_Percentage
0	Afghanistan	2000	0.00	0.0	0.16	0.47	34.042552
1	Afghanistan	2001	0.00	0.0	0.09	0.59	15.254238
2	Afghanistan	2002	0.00	0.0	0.13	0.69	18.840579
3	Afghanistan	2003	0.00	0.0	0.31	0.94	32.978724
4	Afghanistan	2004	0.00	0.0	0.33	0.89	37.078653
...	...	...	...	...	...	...	...
5157	Zimbabwe	2016	3.46	0.0	0.04	6.82	51.319649
5158	Zimbabwe	2017	3.02	0.0	0.03	7.31	41.723666
5159	Zimbabwe	2018	3.69	0.0	0.04	9.13	40.854327
5160	Zimbabwe	2019	3.62	0.0	0.04	11.31	32.360742
5161	Zimbabwe	2020	3.44	0.0	0.04	11.11	31.323133

	Country	Year	Coal	Gas	Oil	Total	Dirty_Percentage
0	China	2021	5339.144043	272.597992	12.248143	8534.250	65.899056
1	China	2020	4920.826172	252.500000	10.799000	7779.060	66.642052
2	China	2019	4855.192871	232.500000	10.528831	7503.428	67.945234
3	China	2018	4763.917480	215.500000	10.861873	7166.133	69.636990
4	China	2017	4430.028320	203.199997	9.868982	6604.447	70.302590
...	...	...	...	...	...	...	...
5157	Kiribati	2001	0.000000	0.000000	0.010000	0.010	99.999998
5158	Kiribati	2002	0.000000	0.000000	0.010000	0.010	99.999998
5159	Montserrat	2000	0.000000	0.000000	0.010000	0.010	99.999998
5160	Montserrat	2001	0.000000	0.000000	0.010000	0.010	99.999998
5161	Montserrat	2020	0.000000	0.000000	0.010000	0.010	99.999998

	Country	Latest_Year	Clean_generation	Dirty_generation	Total	Clean_Percentage	Dirty_Percentage	Clean_Rank	Dirty_Rank	Clean_Percentage_Rank	Dirty_Percentage_Rank
0	China	2021	2690.099976	5623.990178	8534.250000	31.521223	65.899056	1	1	47	27
1	United States	2021	1625.756805	2692.444366	4406.413000	36.895243	61.102860	2	2	42	34
2	India	2021	340.643444	1337.623460	1714.820900	19.864666	78.003683	7	3	58	19
3	Russia	2021	441.821898	710.020799	1157.085800	38.184022	61.362848	6	4	40	33
4	Japan	2021	233.375048	659.267569	1019.721250	22.886161	64.651744	9	5	57	29
...	...	...	...	...	...	...	...	...	...	...	...
74	Lithuania	2021	1.870149	1.360000	4.783984	39.091871	28.428191	65	75	37	60
75	Montenegro	2021	2.180000	1.470000	3.650000	59.726028	40.273973	64	73	23	50
76	Malta	2021	0.210000	2.240000	2.460000	8.536585	91.056913	78	70	71	6
77	Luxembourg	2021	0.665714	0.270000	2.306064	28.867992	11.708263	75	77	52	73
78	Burundi	2021	0.170000	0.140000	0.320000	53.124999	43.750000	79	78	27	46

	Country	Year	Nuclear	Hydro	Wind	Solar	Coal	Gas	Oil	Total	Clean_Percentage	Dirty_Percentage	Country
0	Argentina	1985	5.766	20.653124	0.000000	0.000000	0.607000	12.450280	5.706000	45.303726	58.315565	41.416638	Argentina
1	Argentina	1986	5.711	21.030247	0.000000	0.000000	1.013000	13.251092	7.894000	49.022194	54.549265	45.200123	Argentina
2	Argentina	1987	6.465	21.912518	0.000000	0.000000	1.016000	12.537193	10.078000	52.115280	54.451435	45.344078	Argentina
3	Argentina	1988	5.798	15.259144	0.000000	0.000000	1.586000	21.067162	8.635000	52.499520	40.109212	59.597043	Argentina
4	Argentina	1989	5.039	13.187393	0.000000	0.000000	0.865000	24.336302	7.204000	50.862812	35.834419	63.711188	Argentina
...	...	...	...	...	...	...	...	...	...	...	...	...	...
920	Vietnam	2017	0.000	87.598999	0.350000	0.007992	62.612999	39.955456	0.991100	191.593000	45.908249	54.051847	Vietnam
921	Vietnam	2018	0.000	84.488998	0.487000	0.104992	83.845001	39.916496	0.212300	209.181000	40.673384	59.266280	Vietnam
922	Vietnam	2019	0.000	66.542000	0.722000	5.251790	111.180000	42.184689	1.416000	227.422680	31.885909	68.058599	Vietnam
923	Vietnam	2020	0.000	73.382004	1.073790	10.860000	114.764999	33.951771	1.251600	235.410370	36.241306	63.705082	Vietnam
924	Vietnam	2021	0.000	75.899475	2.391613	25.770420	114.123138	26.223457	0.210715	244.776860	42.512805	57.422630	Vietnam

Major Project¶

Introduction to the project¶

Basic Info¶

Dataset Sources¶

Eco-Friendly Fuel Sources¶

Nuclear¶

Hydro¶

Wind¶

Solar¶

Dirty Fuel Sources¶

Coal¶

Natural Gas¶

Oil¶

Other Datasets relevant to the topic¶

Total Electricty Consumed¶

Work on Nuclear Dataset¶

Section 1¶

Discrepancy¶

Decision¶

Caution!¶

DDL Command¶

Work on Hydro Dataset¶

Section 2¶

Note to the reader¶

Inference and Comparison¶

Work on Wind Dataset¶

Section 3¶

Note to the reader¶

Inference and Comparison¶

Work on Solar Dataset¶

Section 4¶

Note to the reader¶

Inference and Comparison¶

Work on Coal Dataset¶

Section 5¶

Note to the reader¶

Inference and Comparison¶

Work on Natural Gas Dataset¶

Section 6¶

Note to the reader¶

Inference and Comparison¶

Work on Oil Dataset¶

Section 7¶

Note to the reader¶

Inference and Comparison¶

Work on Total Electricity Generated Dataset¶

Section 8¶

Note¶

Data Cleaning¶

Attention!¶

Investigation for missing value of USSR¶

Discrepancy¶

Conclusion¶

Note¶

Further Dataset Synchronisation¶

Section 9¶

Deep Cleansing¶

Integration and Analysis¶

Section 10¶

Integrating the Datasets¶